-- 2012-05-17 EM

alter procedure spx_get_products
	@res xml output
as
begin
	set @res = (
	select c.id, c.title
		,(select id, code, title from dbdata.dbo.products where categ_id = c.id order by title for xml raw('prod'), type) as product
	  from dbdata.dbo.product_categs c 
	 order by c.position
	   for xml raw('categ'), root('root'), type
	);
end;
go

grant execute on spx_get_products to Executer;

/*
USE [dbproc]
GO

DECLARE	@return_value int,
		@res xml

EXEC	@return_value = [dbo].[spx_get_products]
		@res = @res OUTPUT

SELECT	@res as N'@res'

SELECT	'Return Value' = @return_value
*/